Computerized Investing - Spreadsheet Collection

home *** CD-ROM | disk | FTP | other *** search

/ Computerized Investing - Spreadsheet Collection / Spreadsheet Collection.iso / pc / ibm9 / models2.doc < prev next >

Wrap

Text File | 1991-07-17 | 54.5 KB | 1,150 lines

A Spreadsheet Model for Fundamental Valuation By Fred Shipley Computerized Investing The basic premise of fundamental analysis is that investors are generally rational in their approach to the valuation of securities and that financial and economic variables are important in establishing a stock's value. Consequently, the financial analyst or investor who uses fundamental analysis is trying to examine company-specific financial data, such as earnings, sales, profitability, debt-equity ratios and rates of growth, to find companies that offer attractive investment opportunities. This is not the same as saying "find under-valued securities", and that is intentional. There is quite a bit of controversy surrounding the issue of whether investors, either professional money managers or individuals, can consistently find undervalued securities. It is, at best, very difficult. Nevertheless, we are constantly faced with decisions about what to do with our savings and how to allocate money among different securities. There is also some evidence that the stock market may not work quite as efficiently as we would like to think. There might be anomalies in the marketplace, situations in which an investor might be able to discover under- or over- valued securities. With this in mind, we should consider what factors to evaluate in making investment decisions. Our discussion here is not meant to be a complete elaboration of all aspects of fundamental stock analysis, rather we want to examine some of the main points, indicating areas that are particularly susceptible to computer analysis, and providing some techniques for this analysis. Much of computerized fundamental analysis consists of gathering and screening data. There are a number of commercially available programs to do this for you, and they are listed in the Individual Investor's Guide to Computerized Investing. Some access on-line databases; some provide data on diskette. Some cover almost every stock that you could buy; some cover only a limited universe. What we suggest here is what to do with all this data you have collected. We will also present later a data template you need to store the information we will use. BASIC VALUATION MODELS Most basic valuation models rest upon discounting some variable that represents cash flow -- either real or potential -- to the investor. We must discount these cash flows because they will be received in the future and so are not as valuable as cash on hand now. There are several issues involved in this process. First, what is an appropriate measure of cash flow? Second, how do you determine a required rate of return at which to discount those cash flows? Finally, how might those cash flows change in the future? For most analysts, earnings rather than cash flow to investors, is the fundamental variable in valuation. Investors examine the company's reported earnings, examine factors that might change earnings in the future, and then apply some valuation model to those earnings. Oftentimes these analysts will delve deeply into financial statements, examining changes in accounting policies used by the corporation, looking for indications that might give a clue to future changes. In using earnings to estimate value, it is important to remember that earnings do not accrue directly to the stockholder (investor) as cash. Some part of earnings may be paid out in the form of cash dividends but the rest -- and perhaps all -- represents reinvestment in the company, in the hopes of generating future returns. Even this reinvestment of earnings does not represent a cash reinvestment -- reported earnings are not the same as cash flows to the firm, since there are charges against revenues (primarily depreciation) that are not cash outlays. Finally, reported earnings are subject to estimation according to generally accepted accounting principles. It may be very difficult to compare earnings among different companies, and that is exactly what we are trying to do in determining value. THE EARNINGS VALUATION MODEL For those companies that do not pay dividends or those that have interest primarily for their growth potential, an earnings valuation model will be the appropriate approach. The most common earnings valuation model is the price-earnings ratio approach. The price-earnings (P/E) ratio is simply the current market price of the stock divided by the most recent year's earnings. For example, earnings for the Dow Jones Industrial Average for 1987 were $133.05. A 2000 level on the Dow would give a P/E of 2000 P/E = ------- $133.05 P/E = 15 The idea behind this approach is to determine an expected P/E ratio, or P/E multiplier, and use this multiplier to arrive at a value estimate. We could use the current price-earnings ratio, such as we determined above, or more appropriately we could try to anticipate the expected P/E. The equation for the model is P0 = E1 x P/E1 (1) where P0 is the estimated value of the stock now E1 is expected next year's earnings, and P/E1 is the expected (or normal) price earnings ratio -- the ratio of price to projected earnings. Since we have to project figures for next year, we are faced with the issue of estimating a rate of growth for earnings. In particular, we could estimate E1 by the formula E1 = E0 X (1+geps) where E0 is last year's reported earnings per share, and geps is the anticipated growth of earnings per share. THE DIVIDEND VALUATION MODEL The other approach to valuation is to look only at the cash flows an investor actually receives -- the cash dividends the company pays out. This approach is obviously of little direct use for companies that pay no dividends. In a very real sense, however, the only cash an investor may receive from a firm on an investment are the dividends paid out. What about future stock price changes that determine capital gains and losses? The investor who is willing to buy from you in the future will also be looking at cash flows. For this future investor, that cash flow will consist of future dividends and some even more distant stock value. That stock value will in turn depend on future dividends, and so on. Essentially we are arguing that dividends must matter, and other variables are simply ways of trying to get the same kind of information that actual and anticipated cash dividends give us. For the moment, let's only consider expected cash dividends. We don't really care about dividends that have been paid in the past; what we are interested in is what we might receive in the future, once we buy the stock. The most basic dividend valuation approach rests on the assumption that dividend growth can be approximated by a constant annual rate of change -- for example, we might presume that dividends will grow at 5% a year. This estimate is something that will come from the data we have collected, and we will deal with it later. With this information, all we need for an estimate of value is a discount rate, or required rate of return -- that is, a rate that reflects the diminished value of cash received in the future and that compensates us for the risk involved. Let's denote the rate of dividend change by g -- for growth, but recognize that growth could be negative. We will use r for the required rate of return and DPS0 for the most recent annual dividend. With this in mind, the formula for the current value of a stock, P0, is simply the next expected cash dividend, capitalized at the difference between the required rate of return and the expected rate of growth: DPS1 P0 = ------- (2) r - gdiv where DPS1 = DPS0 X (1+gdiv); that is, DPS1 is next year's anticipated dividend, based on last year's actual dividend DPS0, and the estimated rate of growth-- gdiv. From looking at this model, it is clear that when investors need a higher rate of return, perhaps because they foresee greater risk in the market or in the stock, the value of the stock decreases. Conversely, as expectations of growth increase, so does the value of the stock. Of course, the rate of growth must be less than the return investors require or irrational stock values result. The value estimate, P0, is just that. Based on the variables we have examined, we have created a projected value for the security, just as we did using earnings. If the current market price is significantly below this value, the stock appears to be a good buy. If the market price is significantly greater than this value, the stock is a candidate for a sale or even a short sale. The dividend approach to valuation is suitable primarily for larger "blue chip" companies that pay a regular dividend. Smaller growth companies, such as the AAII Shadow Stocks, that pay little, if any, dividends can be more appropriately valued using other techniques. While this valuation technique results in a very simple model, it is important to be aware of the underlying assumptions. We have assumed a constant rate of growth in dividends and a constant discount rate. Both these variables are subject to change over time. When we examine the denominator of the valuation equation (2), we can see that if r and gdiv are close in value, even small changes in our estimates of these numbers can result in substantial changes in stock value. These considerations make this model very appropriate for spreadsheet analysis, since we will be particularly interested in how stock value changes when the input values change. For example, we might have several different estimates of the rate at which dividends will grow in the future. Examining the effect of changes in these estimates is exactly the kind of analysis for which spreadsheets were designed. Moreover, estimation of an investor's required rate of return depends on a few crucial market-related variables, and it is important to understand how changes in market conditions can affect value. ESTIMATING AN INVESTOR'S REQUIRED RATE OF RETURN The rate of return an investor requires depends on the returns available on alternative investments as well as the investment risk. We should certainly expect a return on any risky investment to be greater than what we could earn on a risk free investment such as a U. S. Government Treasury bill. Moreover, we should be able to measure risk relative to some market standard. For stocks that are riskier than the stock market as a whole, we should be able to earn a better return than the market itself offers. Essentially, what we must do is break up total return into the return available from risk-free investments such as T-bills, and a return that is compensation for the risk involved in a stock. Stock is risky for a number of reasons. First, there is the risk that is inherent in the company itself. For example, the company may suffer a strike, it may suffer from adverse litigation or from a natural disaster. By changing its financial structure, financing more by debt, as is common in leveraged buyouts, the company can increase the risk to its stockholders. These firm-specific risks can be eliminated by holding a portfolio of stocks. Then an adverse circumstance affecting one company will have little impact on the overall value of the portfolio -- indeed it may be offset by beneficial effects on another company in the portfolio. As a consequence, an investor with even a reasonably small, but well-diversified, portfolio of 15 stocks or so can minimize such firm-specific risk. This is important because investors cannot expect to be compensated for taking risk that they can easily and cheaply be rid of. Second, there is market-related risk. This is the risk inherent in the variability of the market itself. General changes in the economy will affect all firms -- that is, the market itself. All stocks share a general sensitivity to changes in market conditions; but some stocks may be more or less sensitive. Since we cannot diversify away this risk, all investors require compensation for taking it. A well-diversified portfolio has only market risk. We measure this risk by a stock's BETA. The market has a beta of 1. A stock that is more sensitive to changes than the market as a whole will have a beta greater than 1; a stock that is less sensitive to changes than the market as a whole will have a beta less than 1. For example, a stock with a beta of .8 varies only 80% as much as the market as a whole. A stock with a beta of 1.45 varies 45% more than the market as a whole. The lower beta stock should offer investors a lower return than the market; the higher beta stock should earn a higher return than the market; and both should earn a greater return than T-bills, which have a beta of 0. The model that determines an investor's required rate of return based on these factors is: r = RRf + BETA(RM - RRf) (3) where RRf is the expected return on a risk-free investment, such as Treasury bills BETA is the risk of the stock relative to the market as a whole RM is the expected return on a broad measure of stock market performance, such as the Standard and Poor's 500 Composite Stock Index. (RM - RRf) is the expected equity risk premium; that is, the extra return offered by the average stock. Where does an investor gather this information on expected returns? We can use historical data to estimate the equity risk premium. Over the last 60 years or so, equities have on average offered about 6.5% greater return per year than the return on Treasury bills. This would be a reasonable number to use for a long term investment approach. We could use the current return on T-bills as an estimate of expected returns. Using the current rate on 3 month maturity T-bills of about 6.2%, and a beta of 1.0 for the average stock, the model would give a required return of r = 6.2% + 1.0(6.5%) r = 12.7% If we do not want to use the current Treasury bill rate, we can estimate a risk-free return from the anticipated rate of inflation. Over the past 60 or so years, the Treasury bill return has been approximately equal to the annual rate of inflation. This means that we could estimate the anticipated Treasury bill return by a rate equal to the anticipated rate of inflation. A more conservative approach would be to include a real rate of return in addition to an inflation adjustment. Although the data for the last 60 years indicate that the real rate of return has been close to zero, more recent data suggest that a real return of 2% to 3% is appropriate. If we expected the rate of inflation over the next year to average 4.5%, that would make the return on T-bills 7% -- the 4.5% inflation premium plus the midpoint 2.5% real rate of return -- slightly higher than the current return. So the equation to estimate the expected risk-free rate of return is RRf = Rr + CPI RRf = 2.5% + 4.5% RRf = 7% where Rr is the real rate of return of 2.5%, and CPI is the anticipated rate of inflation, as measured by the Consumer Price Index. Another way of looking at the dividend model is in terms of investors' expected rate of return: DPS1 r = ------ + gdiv (4) P0 In this restated fashion, an investor's return is the stock's anticipated dividend yield plus the expected growth in dividends, which works out mathematically to be the anticipated capital gains return. We could compare this expected rate of return to our required return to find whether a security was over- or under-valued. Suppose for example that we found that the expected return for our average (beta = 1) stock was 15%. Since we determined that we only required a return of 12.7%, this stock would be a good buy. On the other hand, if our expected return was only 11%, we should sell the stock (or sell it short). Estimates of Growth We have seen that growth is one of the most important variables affect- ing value, whether of dividends or of earnings. We will now focus on the process of estimating growth rates -- determining the rate of growth from historical data and other financial variables. We can approach the issue of estimating growth in several ways. One method determines the annually compounded rate of growth. Another approach is to do a trend analysis, estimating the rate of increase from historical data. We will discuss each here. We will focus on dividends to illustrate the process, but the same principles apply to all of the variables we use. Perhaps the easiest start is to determine the annually compounded growth rate. This is simply the rate that makes our initial dividend compound, or increase, to the amount of the most recent annual dividend. Looking at the data for IBM, you can see that IBM's dividend was $3.44 in 1980 and that it grew to $4.73 in 1989. There are nine years of growth from the end of 1980 through 1989. So we must determine the growth rate gdiv that makes $4.73 = $3.44(1+gdiv)^9. That is, gdiv is the rate of increase that would make $3.44 compound to $4.73 after 9 more years. This may look like a formidable task, but it is really quite simple. Since we want to determine gdiv, we get gdiv = ($4.73/$3.44)^(1/9) - 1. (5) gdiv = .036 or 3.6% Looking at the data we can see that this has not been a truly steady rate of growth. Dividends did not increase at all for a number of years. You should remember though, that we are looking at this information to estimate a long run value. And over the ten years from 1980 through 1989, the long run increase has been 3.6% a year. Another approach to accounting for the year to year dividend changes is doing a trend analysis. What this technique does is determine the best (in a statistical sense) estimate of the change over time. We have some difficulty applying this technique to growth rates though, since a constant rate of growth does not result in a straight line change. Since we are concerned with the growth rate, not the dollar change, we want some way to visualize the change in dividends over time as a constant percentage rate of growth. The way around this is to use logarithms (or logs), since the rate of growth can then be portrayed by a straight line graph. Essentially what taking the logarithm does is compress the scale of the vertical axis so that a constant percentage rate of growth is represented by a straight line. Normally a constant dollar change is represented by a straight line. We are concerned, however, not with a constant dollar change, but rather a constant percentage rate of growth. That is what using logarithms allows us to do, and using the natural logarithm is equivalent to determining a constant rate of growth, continuously compounded. Fortunately spreadsheet programs typically include a log function, which automatically determines these values. In Lotus 1-2-3, Release 2 and higher and Microsoft Excel, there is a built-in regression analysis which will determine these trend values for us. In the earlier version (Release 1A) and in VP-Planner, we will have to create the formulas to determine the trend values ourselves. We have not dealt with the company's ability to sustain past growth into the future. In part that depends on the profitability of new investments and the demand for the company's products. Our analysis does not allow us to estimate future demand; it only allows us to project profitability from historical data. In subsequent sections we will examine in more detail some of the interplay of the factors affecting this profitability. This will allow us to understand more fully some of the underlying relationships among the variables that are important in determining value. We are now in a position to begin to integrate those different growth estimates with other techniques for projecting earnings. This will provide the necessary numbers for our valuation models. To begin we will look at some relationships between company-specific factors that affect future earnings potential. What we want to determine is how management decisions can affect earnings growth potential since this in turn, affects value. Sustainable Growth The final growth concept to estimate is called sustainable growth. Sustainable growth represents the growth the company can maintain without changing its pattern of financing and without seeking external funding. These are important considerations for investors since they affect the risk investors bear. For example, if the company changes its debt-equity ratio (the ratio of total short and long term debt to total stockholders' equity) by increasing debt, the stockholders face additional risk. There is a greater fixed outlay for the company to pay the increased interest on this additional debt. Since there is the potential for a proportionally greater cash outlay to the bondholders, there may be less to distribute to the stockholders or reinvest for future growth. The sustainable growth rate can be determined by gsus = ROE x b (6) where gsus is the sustainable rate of growth ROE is return on equity -- that is, net income divided by total common equity, and b is the earnings retention ratio -- that is, the percentage of earnings reinvested in the business, and not paid out in dividends. We may be more used to thinking of the dividend payout ratio, dividends divided by earnings. The earnings retention ratio is just the converse of the payout ratio. In fact we can determine the retention ratio by subtracting the payout ratio from 1. DPS b = 1 - --- (7) EPS where DPS is dividends per share, and EPS is earnings per share. Referring to our previous data for example, in 1989 IBM earned $10.65 and paid $4.73 in dividends. So their payout ratio (1-b) was DPS $4.73 --- = ----- = .444 or 44.4% EPS $10.65 The retention ratio in turn is b = 1 - .444 = .556 or b = 55.6% Return on equity can vary considerably from year to year, so we will simply determine an average by dividing average EPS by average book value (also called net worth and common equity) per share. IBM's average per share book value is $48.48 and its average earnings per share is $8.66. This gives us an ROE of $8.66 ROE = ------ = .179 or 17.9%. $48.48 With this information the sustainable growth rate is gsus = ROE x b = .179 x .556 = .100 or 10.0% (We have rounded these numbers off to make the presentation clearer. When you program the formulas into your spreadsheet, you may get a slightly different display. Remember that the program uses the full power of any number it calculates, even if rounded numbers are displayed. Lotus will store values to 99 decimal places and calculate with even greater accuracy.) Rather than focus entirely on direct estimates of the growth in earnings per share, we should analyze some variables that underlie earnings. By breaking down earnings into component parts we get better insight into the company's current and possible future position. There are several ways to do this. First we will look at sales and profit margins. We can determine earnings per share by multiplying sales per share by the net profit margin (pm). EPS = SPS x pm (8) where SPS is net sales divided by the number of common shares outstanding, or sales per share, and pm is the company's average profit margin One quick way of estimating the average profit margin to divide average EPS by average SPS. For IBM this gives us $ 8.66 pm = ------ = .114 or 11.4% $75.95 With this information, we need an estimate of next year's sales in order to estimate next year's earnings. Taking our annually compounded sales growth rate (gs) of 10.5% we would estimate next year's (1990's) sales by SPS1 = SPS0 x (1+gs) so SPS1 = $110.35 x (1.105) or SPS1 = $121.94 This would make 1990's earnings EPS1 = SPS1 x pm or EPS1 = $121.94 x (.114) so that EPS1 = $13.90 Using the trend rate of growth we would estimate 1990 earnings at $13.89. The final approach to earnings estimation requires us to look at the relation between book value per share and earnings per share. Here we are saying that earnings can be estimated by projecting book value times the return on that book value. Once again we will use average return on equity and project 1990 book value. With this approach, book value for 1990 is BV1 = BV0 x (1+gbv) so BV1 = $71.90 x (1.11) or BV1 = $79.81, using the annually compounded growth rate. With the trend growth rate of 11.9%, projected book value would be $80.46. Then earnings projected for 1990 will be EPS1 = BV1 x ROE or EPS1 = $67.38 x .196 so EPS1 = $13.21 The important factors to remember are that earnings numbers represent the accountants' best estimate of performance, given the application of generally accepted accounting principles. These principles allow for considerable differences in reported earnings, especially given the choices of inventory valuation and depreciation (accelerated or straight line) methods. For example, a company that uses accelerated depreciation will show lower reported earnings since it is showing a larger depreciation expense on its income statement. A company using straight line depreciation will report higher earnings. Despite the differences in reported earnings per share, both companies will use accelerated depreciation for their tax returns and will have the same cash flow (other factors being the same). Though the company with the higher reported earnings may appear to be doing better, in reality there is no difference between the two. It is important to ensure that the numbers you use correctly reflect consistent accounting practices. This may mean that updating a company's information will require a complete revision of data, rather than just adding the most recent year, since the company may restate figures for several past years. Exercise care when entering new information to see that it is consistent with the past data. Determining a Price-Earnings Ratio Having determined an earnings estimate from the range of possibilities we examined above our next job in calculating a value is to apply a reasonable price-earnings ratio. We will look at three ways of doing this. Our first approach is simply to look at historical P/Es. The upper part of the spreadsheet contains the calculated high and low price-earnings ratios for IBM for each of the last 10 years, as well as average figures. We can estimate a range of stock values by using these 10 year average high and low P/Es with our average earnings estimate. This approach though, does not take direct account of market factors, and we should adjust for these factors. Our second approach is to examine market-relative P/Es. The relative price-earnings ratio is simply the company P/E divided by the market's P/E. Company P/E P/E Relative = ------------- Market P/E Analysts use this figure to examine values relative to the market. One would expect smaller, growth companies to sell at higher P/Es than the market (the relative should be greater than 1), while more mature, perhaps even declining companies, should sell at a discount to the market (the relative should be less than 1). Companies that consistently sell at a premium to the market should definitely be growing at a faster than average rate. On the other hand, companies that are selling at a discount to the market may represent potential turnaround candidates, especially if they have been selling at or near the market in the past. We will base our relative P/Es on the average highs and lows so as to obtain a range of values once again. Average High Co. P/E High P/E Relative = ----------------------- Average High Market P/E and Average Low Co. P/E Low P/E Relative = ----------------------- Average Low Market P/E Determining the relative P/E is only the first step in this analysis. The next step is to find the current market P/E and multiply the relative P/E by the current market P/E to obtain the current company P/E. Company P/E = Relative P/E x Current Market P/E You must be careful in applying this approach during market peaks and troughs. The relative P/E was developed over several years of performance and represents an average valuation relative to the market. The high P/E relative was developed by taking the company's average high P/E and dividing it by the average high market P/E. Generally market and company high P/Es will occur at different times and for different reasons. It may be more difficult, for example, for a stock that has a relative P/E premium to maintain that same premium when the market P/E is also high -- a situation such as existed in the summer of 1987. It is quite likely that such a situation will result in very high value estimates and you should take them with a large grain (or several smaller ones) of salt. The Graham-Dodd Earnings Multiplier The last market-adjusted P/E approach was developed by Graham and Dodd in their classic investment text, Security Analysis. Graham and Dodd's text is still regarded as one of the most important expositions of the fundamental approach to security valuation. Based on some simple historical data, they observed a statistical relationship between P/Es and growth, one which we have already seen on a theoretical basis. Their original P/E multiplier was P/E = 8.5 + 2G, where G is the rate of earnings growth as a percentage. We will use a capital G to indicate that a percentage, rather than a decimal number is required here. For example, using IBM's trend rate of earnings growth of 6.4%, we would estimate their P/E as P/E = 8.5 + 2(6.4) = 21.3 This is obviously quite high in comparison to the P/E ratios IBM has experienced over the last ten years. Another factor that must be considered in this model is the time period over which this growth and P/E relationship was estimated. This relation was established on the basis of data from the late 1950s and early 1960s. Clearly the general levels of both interest rates and inflation have changed significantly since then. Since interest rates and inflation tend to be closely related, we can modify the Graham- Dodd multiplier with an interest rate adjustment. This adjustment reduces the growth part of the multiplier by the ratio of 4.4%, the AAA bond yield that prevailed when the growth relationship was established, to the current AAA bond yield. Adjusted P/E Multiplier = (8.5 + 2G)(4.4%/AAA Bond Yield), where AAA bond yield is the current yield to maturity on long term AAA-rated bonds, as a percentage. Taking the recent AAA bond yield of 9.4% we would estimate IBM's adjusted P/E as P/E = (8.5 + 2(6.4))(4.4/8.87) = (8.5 + 12.8)(.4961) = (21.3)(.4961) = 10.57 These techniques, however, give us a range of price-earnings ratios that we can use to estimate value. Overall we have quite a bit of variation -- from about 10 to more than 15. This situation is not at all unusual, especially given a 10 year historical perspective that would normally include several market cycles. The judgement of the investor must come in now, evaluating the relative state of the market over the next year or two. Should the company fall at the lower or upper end of the range of P/E ratios we have estimated? What factors are there in the company's current situation that might affect valuation relative to the market? Is the company in or near a turnaround situation that might lead to a sudden increase in their P/E? The Dividend Yield Dividend yield measures the current income return from a stock. It is calculated by dividing the annual dividend by the stock's price. To get a range of values we will use both the annual high and low prices. Annual Dividend High Dividend Yield = --------------------- High Market Price A similar calculation is performed using the low price for each year. The Profit Margin and the Return on Equity The company's profit margin measures its profitability as a percentage of revenues. While this is an important measure of managerial efficiency, it does not tell us the whole story. An investor must also examine how effectively the company is employing its assets. From an investors viewpoint, this overall profitability can be measured by return on equity (ROE). Earnings Per Share Profit Margin = ---------------------- Sales Per Share Earnings Per Share Return on Equity = ------------------------ Book Value Per Share Other Valuation Approaches As a final fix on establishing a possible range of values for IBM, we will compute some other ratios that are commonly examined in value estimation. These ratios are the market price to sales, price to dividends (the inverse of the dividend yield) and price to book value. To determine the market price used in these ratios, we will compute an average price for the company. This is simply the average of the high and low average prices over the ten year period for which we have data. The average high price for IBM is $126.21. The average low price for IBM is $89.65. The overall average price is then AVERAGE HIGH + AVERAGE LOW PRICE AVERAGE PRICE = ------------------------------------ 2 $126.21 + $89.65 = -------------------- 2 AVG. PRICE = $215.86/2 = $107.93 The historical relation between market price and sales is simply the average price per share divided by the average sales per share. Thus we have AVG. PRICE AVG. PRICE/SPS = -------------- AVG. SPS For IBM, this is $107.93 AVG. PRICE/SPS = --------- $75.95 AVG. PRICE/SPS = 1.42 On average then, IBM's stock price has been about 42% greater than its sales (on a per share basis). Since sales revenues provide the basis from which profitability and cash flows come, the ratio of price to sales may give us a more stable market value relationship than price to earnings. In order to complete the valuation, we take this price to sales ratio and multiply it by our projection of sales for the coming year. In this example, we simply use the trend rate of sales growth. Projected Value = Projected Sales x Price/Sales = SPS1 x 1.42 = SPS0 x (1 + gs) x 1.42 = $121.94 x (1.15) x 1.42 = $121.94 x 1.42 = $173.15 Since dividends tend to be more stable than earnings, we may also get a better valuation estimate from a relationship between dividends and market value than we can from a price to earnings model. Indeed looking at dividend yields and their historical trends would have strongly suggested an overvaluation of the market before the "break" in October 1987. Dividend yields (and their converse, the price/dividend ratio) tend to be more stable -- in "normal" markets -- than price-earnings ratios. To examine this relationship, we determine the price to dividend ratio. AVG. PRICE AVG. PRICE/DPS = --------------- AVG. DPS AVG. PRICE/DPS = 26.68 Thus IBM's stock price has been more than 26 times its annual dividend. (This is the same thing as saying that the company's dividend yield has been a little under 4% -- about 3.8%.) Applying this valuation model to IBM and using the annually compounded rate of growth in dividends we get Projected Value = Projected Dividends x Price/Dividends = DPS0 x (1 + gdiv) x 26.68 = $4.73 X (1.036) x 26.68 = $4.90 x 26.68 = $130.74 The last price relationship we want to explore is a very standard one - - the relation between price and book value. By itself book value is often taken as a bottom line or lower end estimate of value, since assets are carried on a company's books at (depreciated) cost. Given this depreciated cost, growth of book value will appear unusually large, since the growth reflects additions to book value at current costs. For most companies, there will not usually be a consistent relation between book value and market price. This number is important, however, for certain types of companies. For example, the return allowed to public utili- ties is usually based on the book value of their assets. Since that return determines profitability and cash flow, the market price will be more consistently related to book value. Also for companies that are considered takeover candidates as "asset plays", the book value of those assets will play a role in estimating the value in a takeover. Often times the market value of a company's assets will be considerably greater than their book value. This was the justification for many of the mergers in the energy fields in the 1970s, for example. The ratio of price to book value is AVG. PRICE AVG. PRICE/BVPS = ---------------- AVG. BVPS AVG. PRICE/BVPS = 2.23 Thus IBM has traded, on average, at about 2' times its book value per share. This certainly reflects the high cash flow and profit margins that IBM has historically maintained. Determining value from this relationship requires projecting book value per share for next year. We will use the annually compounded rate of growth in book value in this example. Projected Value = Projected Book Value x Price/Book Value = BVPS0 x (1 + gbv) x 2.23 = $71.90 X (1.1097) x 2.23 = $79.79 x 2.23 = $177.93 The valuation estimates from these last three approaches are in line with the P/E models we developed earlier. The dividend approach again gives the most conservative value estimate. It is not surprising that the sales and book value approaches give higher valuations since they implicitly assume that the company will be able to maintain its profitability (profit margin) and the higher growth rates often associated with sales and book value. This completes the valuation models we will use in this analysis. As you can see we have determined a fairly substantial range of estimates. The real problem and the important question now to be resolved is where the value should be within this range. We will now set up a spreadsheet model to hold the data and valuation models. Setting up the Dividend Valuation Models In order to use this technique, we need to know the risk free rate of return, the difference between the market return and the risk free return and the security's beta. For the risk free return, we take the recent 3 month Treasury bill return, which is currently about 6.2%. For the market risk premium, we can take the long term historical average of about 6.5%, and for beta we can take the number provided by Value Line, which is 1.0 for IBM. These numbers gave us a required (minimum acceptable) rate of return for IBM of 12.7%. For the rate of growth, we can start with the historical annually compounded rate of dividend growth of 4.8% or the trend rate of dividend growth of 4.5%. When we examine the sensitivity of these estimates to changes in the input variables, we will use a range of values for growth that will include the forecasted range. You can get estimates of growth from Value Line, Standard and Poor's and other services. Using the 4.8% figure gives us projected dividends for 1988 of D1 = D0 x (1+gdiv) = $4.73 x (1.036) = $4.90 This is turn gives us an estimated value of $4.90 $4.90 P0 = ---------- = ------- .142-.036 .106 = $46.22 This value is considerably less than IBM's recent price of $116.25 (close, Sept. 12 1989). What factors seem most important in generating this difference between the theoretical value and the market value? This is always one of the crucial questions the analyst must answer. It is only through this part of the valuation analysis that you can gain any insight into the market factors and psychology affecting value and the individual components that are crucial in the stock analysis process. One way of answering this question is to turn around the valuation equation and look at the market's assessments of growth and required returns. For example, we could determine the market's estimate of return, using the 3.6% growth rate of dividends and the current market price of $114. $4.90 Expected r = ------------- + .036 $114 = .043 + .036 = .079 or 7.9% This implied rate of return on IBM is a little low compared with long term trends. Remember that IBM has a beta of 1.0 so it is just as risky, and so should return just the same, as the market. Also remember that his- torically the Standard and Poor's 500 stock index has averaged about a 10% annual return. The implied market return, given the current price of $114 is a bit low. Remember that we generated our value of $46.21 by using a rate of return of 14.2%. This return is high by long term historical standards. The titles and formulas are entered in rows beginning with row 101. This allows us to keep the dividend valuation input variables and the resulting valuation on a separate screen. The current value of the 3 month Treasury bill rate must be entered into cell F109, the market risk premium must be entered into cell F110 and the security's beta goes into cell F111. The Treasury bill rate may be obtained from a number of sources, including Barron's, the Wall Street Journal, the Value Line Investment Survey, and Standard and Poor's Outlook. The company's beta is easily obtained from Value Line or Standard and Poor's Stock Reports. The spreadsheet then calculates the required rate of return for the company and the value based on that return. DATA INPUT TEMPLATE The company information that we will use can be obtained from a number of sources, including the Value Line Investment Survey, Moody's or Standard and Poor's stock reports, or from the company directly. As an example, we have used IBM, with data through 1987. For whatever company you might wish to analyze, be careful to set up your data spreadsheet in exactly the same format. The formulas that we will provide later assume that the data are in exactly the indicated locations. The first step is to set up a spreadsheet template with the data we will be analyzing. In the first column (A in Lotus 1-2-3, 1 in Multiplan) starting in row 6, enter the years for which you have gathered data. In the second column (B) put the sales per share (SPS). Value Line provides this information directly, but it can also be calculated by dividing total revenues (or sales) by the number of shares outstanding. Dividends per share (DPS) go in column C, earnings per share (EPS) in column D, cash flow per share (CFPS), and book value per share (BVPS) in column F. Book value per share can be calculated by dividing the total value of common equity (including retained earnings and capital) by the number of shares outstanding. The next two columns contain the stock's highest and lowest closing prices for the preceding year. We use these to determine price-earnings ratios. Remember that you do not enter the $ signs for these variables, use the formatting capability of your spread-sheet to generate the appropriate style. Once you have entered the data, you should save the file so that you can retrieve it later. We will continue to program the rest of the spreadsheet, but will save the formulas under a different file name. This is allow us to have a general worksheet with all the formulas we need and we can just combine the company data file later. We used some averages to estimate factors affecting growth. Let's determine these averages for the variables we are tracking. On line 17, in column A, enter the title AVERAGE. Then put the formula @avg(B6..B15) into cell B17 and copy it over to cells C17 through L17. If there are no data in some of these columns, the spreadsheet will display an error message ERR. Do not worry; as soon as you enter data into the appropriate columns, the formula will work correctly and display the value we need. We also want to determine price-earnings ratios for the years for which we have data. We have seen that one of the basic valuation techniques involves the estimation of value from a projected earnings and a price-earnings ratio. Since we have collected a high and low stock price for each year, we will determine both a high and low price-earnings ratio for the year. To determine the high price- earnings ratio we divide the year's high stock price by the year's earnings. So in cell I6 insert the formula +G6/$D6 and copy that through the range from I6 to J15. You should then have a high price- earnings ratio for each year from 1978 to 1987 in column I and a corresponding low price-earnings ratio in column J. Finally we will need high and low price-earnings ratios for the Standard and Poor's 500 Stock Index (or some other index of the market) and these will go in columns K and L, corresponding to the correct years. The next step is to program in the formulas for determining growth. Enter the appropriate titles into cells A22 and A23 and program the formula (B15/B6)^(1/9)-1 into cell B24. Then copy that formula into cells C24 through F24. Calculating the trend rates of growth is a little more complicated. There are two possibilities for calculating the trend growth rates. If you have Lotus 1-2-3, Release 2 or a program that is compatible with it, you can simply use the built in regression feature to determine the growth rates. If you do not have a regression feature, you will have to program the formula directly. Using Trend Analysis in Lotus Release 1A and Compatible Programs Since we want to determine a growth rate, we must transform all our data into logs. To do so, create a series of columns to the right of the basic data area, one for each input variable. Insert the formula @ln(B6) in cell X6. This formula simply tells Lotus to display in cell X6 the natural logarithm of the contents of cell B6 -- 1978 sales on a per share basis. We do have to exhibit some care, however, since logs are only defined for non-negative numbers. If we encounter a situation with negative earnings, for example, we will have to skip that data. We can modify the formula we put into cell L6 so that it will indicate if there is an error encountered. We could put the formula @if(B6>0,@ln(B6),@err) into cell X6. Then anytime Lotus finds a number that is zero or less, it displays ERR in the appropriate cell. Any formula that makes reference to that cell would also have ERR displayed there. About the only thing we can do in this case is ignore the bad data. Simply blanking out the cell with the offending data will do the trick. If there are a number of years with negative earnings, any forecast you get will be very suspect. While you can apply the formulas, the results will not be valid. Do not try to use this approach in such cases. Now copy the formula you entered into cell X6 throughout the range from X6 to AB15. These values are the ones used in determining the regression. We also need to create a column for the number of time periods we are using in the analysis. In column AC enter the numbers from 1 to 10 into rows 6 through 15. In column AD we need the squares of these numbers so enter into cell AD6 the formula +$AC6^2. Copy this formula down from AD6 through AD15. Finally in columns AE through AI we need another formula. In cell AE6, enter the formula +$AC6*X6. Copy this formula to the entire range from AE6 through AI15. The formula for the trend estimate of the growth rate of sales is: @EXP((@COUNT(X6..X15)*@SUM(AE6..AE15)- @SUM($AC6..$AC15)*@SUM(X6..X15))/(@COUNT(X6..X15)*@SUM($AD6.. $AD15)-@SUM($AC6..$AC15)^2)))-1 It is important to enter the $s exactly as indicated since we want to lock in references to columns AC and AD which contain values that apply to every variable. The remaining values we want to adjust when we copy the formula over. Enter this formula into cell B24 and copy it to cells C27 through F27. Instructions for Using the Data Regression Feature in Lotus Release 2 and Compatible Programs. Determining the trend results is a simple application of Lotus regression commands in Release 2. Just type / D(ata) R(egression) to bring up the regression menu. You will be asked to specify the X- range (that is, the independent variable -- time periods in our case). Highlight column AC, rows 6 through 15. You must also indicate the Y- range, the dependent variable, which will respectively, be the log of sales, dividends, earnings cash flow and book value per share (columns X, Y, Z, and AA, respectively). You will have to indicate an output range, the area of the spreadsheet where you want to place the results. The rows below the transformed variables (log SPS, etc.) provide a good location. You will have to provide a separate output range for each variable so that Lotus does not simply write over the results from a previous variable. Programming the Spreadsheet to Project Earnings To make it relatively easy to see what is happening, we will create a separate screen of information with our growth estimates and earnings projections. We will use an average earnings per share forecast initially use in our valuations. The valuation results will appear on yet another screen. Our growth estimates already are programmed into the range from B22 through F27 and appear naturally on a second screen. Let's move down a few lines to enter some of the calculations we will need for these projections. In setting up the earnings valuation section of the spreadsheet, we will include a part for a simple application of the average P/E ratios we have from our historical data and for the market relative P/E. Another screen of this section will include the Graham and Dodd adjusted P/Es and valuation. In addition, we will program the other valuation techniques we have discussed, including the ratios of price to sales, price to dividends and price to book value, starting in row 90. Saving the Data and Formula Spreadsheets You should have already established a worksheet with you basic data. We also want to save the valuation formulas so that we can use them with other companies' data. To do this, we will simply erase the data from our spreadsheet that it particular to IBM. Use the Range Erase command to blank out cells A2 through F2, and from A6 through H15. Then save the worksheet template, using a name like MODELS. In order to use the MODELS template with another company's data, you must first create (and save) the data for the company you are interested in evaluating. Then simply retrieve the MODELS template. Adding the data for the company is a matter of combining that data with the formulas in the MODELS worksheet. To combine the company's data, make sure your cursor is positioned at the HOME position (cell A1). Use the File Combine command to bring the data into the existing MODELS template. The command will prompt you to indicate how you want the data entered. You will respond with COPY since this will copy the data into the cells you want. If you have saved the data file correctly, you will combine the Entire File as the next prompt will suggest. Lotus will then provide a listing of the available files. Simply highlight the name of the file with the data you want and press Enter or Return. The program will then copy the data you need and the formulas will all display the correct values. You are now ready to evaluate your company. Good luck and many happy returns! (c) Copyright 1988 by the American Association of Individual Investors